package com.flyu.dao.bill;

import com.flyu.dao.BaseDao;
import com.flyu.pojo.Bill;
import com.flyu.pojo.Provider;
import com.mysql.cj.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BillDaoImpl implements BillDao{
    // 获得订单列表
    public List<Bill> getBillList(Connection connection, String productName, int providerId, int isPayment, int currentPageNo, int pageSize) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Bill> bills = new ArrayList<Bill>();   // 结果集
        if (connection != null){
            StringBuffer sql = new StringBuffer();

            sql.append("select b.*, p.proName as providerName from smbms_bill b, smbms_provider p where b.providerId = p.id");  // 将bill表和provider表拼起来
            List<Object> list = new ArrayList<Object>();    // 存放参数
            if (!StringUtils.isNullOrEmpty(productName)){   // productName非
                System.out.println("productName:"+productName);
                sql.append(" and b.productName like ?");
                list.add("%"+productName+"%");
            }
            if (providerId > 0){    // 供应商
                System.out.println("providerId:"+providerId);
                sql.append(" and b.providerId = ?");
                list.add(providerId);
            }
            if (isPayment > 0){
                System.out.println("isPayment:"+isPayment);
                sql.append(" and b.isPayment = ?");
                list.add(isPayment);
            }
            sql.append(" order by creationDate DESC limit ?,?");
            currentPageNo = (currentPageNo-1)*pageSize; // 下页起始位置
            list.add(currentPageNo);
            list.add(pageSize);
            Object[] params = list.toArray();
            rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);
            while (rs.next()){
                Bill _bill = new Bill();
                _bill.setId(rs.getInt("id"));
                _bill.setBillCode(rs.getString("billCode"));
                _bill.setProductName(rs.getString("productName"));
                _bill.setProductDesc(rs.getString("productDesc"));
                _bill.setProductUnit(rs.getString("productUnit"));
                _bill.setProductCount(rs.getBigDecimal("productCount"));
                _bill.setTotalPrice(rs.getBigDecimal("totalPrice"));
                _bill.setIsPayment(rs.getInt("isPayment"));
                _bill.setCreatedBy(rs.getInt("createdBy"));
                _bill.setCreationDate(rs.getTimestamp("creationDate"));
                _bill.setModifyBy(rs.getInt("modifyBy"));
                _bill.setModifyDate(rs.getTimestamp("modifyDate"));
                _bill.setProviderId(rs.getInt("providerId"));
                _bill.setProviderName(rs.getString("providerName"));
                bills.add(_bill);   // 添加到结果集
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return bills;
    }
    // 根据查询条件统计记录总数
    public int getBillCount(Connection connection, String productName, int providerId, int isPayment) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        int count = 0;
        if (connection != null) {
            StringBuffer sql = new StringBuffer();
            sql.append("select count(1) as count from smbms_bill b,smbms_provider p where b.providerId = p.id");  // 将bill表和provider表拼起来后统计数量
            List<Object> list = new ArrayList<Object>();    // 存放参数
            if (!StringUtils.isNullOrEmpty(productName)) {   // productName非空
                sql.append(" and b.productName like ?");
                list.add("%" + productName + "%");
            }
            if (providerId > 0) {    // 供应商
                sql.append(" and u.providerId = ?");
                list.add(providerId);
            }
            if (isPayment > 0) {
                sql.append(" and u.isPayment = ?");
                list.add(isPayment);
            }
            Object[] params = list.toArray();
            System.out.println("BillDaoImpl-->getBillCount: "+sql.toString());  // 输出完整的sql语句
            rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);
            if (rs.next()){
                count = rs.getInt("count");
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return count;
    }
    // 根据id删除订单
    public int delBillById(Connection connection, int billId) throws SQLException {
        PreparedStatement pstm = null;
        int execute = 0;

        if (connection != null){
            String sql = "delete from smbms_bill where id = ?";
            Object[] params = {billId};
            execute = BaseDao.execute(connection, pstm, sql, params);
        }
        BaseDao.closeResource(null, pstm, null);
        return execute;
    }
    // 根据id查询订单
    public Bill getBillById(Connection connection, int billId) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        Bill bill = null;

        if(connection != null){
            String sql = "select b.*, p.proName as providerName from smbms_bill b, smbms_provider p where b.providerId = p.id and b.id = ?";
            Object[] params = {billId};
            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            bill = new Bill();
            if (rs.next()){
                bill.setId(rs.getInt("id"));
                bill.setBillCode(rs.getString("billCode"));
                bill.setProductName(rs.getString("productName"));
                bill.setProductDesc(rs.getString("productDesc"));
                bill.setProductUnit(rs.getString("productUnit"));
                bill.setProductCount(rs.getBigDecimal("productCount"));
                bill.setTotalPrice(rs.getBigDecimal("totalPrice"));
                bill.setCreatedBy(rs.getInt("createdBy"));
                bill.setCreationDate(rs.getTimestamp("creationDate"));
                bill.setModifyBy(rs.getInt("modifyBy"));
                bill.setModifyDate(rs.getTimestamp("modifyDate"));
                bill.setIsPayment(rs.getInt("isPayment"));
                bill.setProviderName(rs.getString("providerName"));
                bill.setProviderId(rs.getInt("providerId"));
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return bill;
    }
    // 保存修改
    public int modifySave(Connection connection, Object[] params) throws SQLException {
        PreparedStatement pstm = null;
        int execute = 0;

        if (connection != null){
            String sql = "update smbms_bill set billCode=?, productName=?, productUnit=?, productCount=?, totalPrice=?, providerId=?, isPayment=? where id=?";
            execute = BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(null, pstm, null);
        }
        return execute;
    }
    // 添加订单
    public int addBill(Connection connection, Bill bill) throws SQLException {
        PreparedStatement pstm = null;
        int execute = 0;
        if (connection != null){
            String sql = "insert into smbms_bill (billCode, productName, productDesc, " +
                    "productUnit, productCount, totalPrice, isPayment, createdBy, " +
                    "creationDate, providerId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            Object[] params = {bill.getBillCode(), bill.getProductName(), bill.getProductDesc(),
                    bill.getProductUnit(), bill.getProductCount(), bill.getTotalPrice(), bill.getIsPayment(),
                    bill.getCreatedBy(), bill.getCreationDate(), bill.getProviderId()};
            execute = BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(null, pstm, null);
        }
        return execute;
    }
    // 根据供应商id，查询对应的订单
    public List<Bill> getBillByProid(Connection connection, int proid) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Bill> billList = new ArrayList<Bill>();
        if (connection != null){
            String sql = "select * from smbms_bill where providerId = ?";
            Object[] params = {proid};
            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            while (rs.next()){
                Bill _bill = new Bill();
                _bill.setId(rs.getInt("id"));
                _bill.setBillCode(rs.getString("billCode"));
                _bill.setProductName("productName");
                _bill.setProviderId(rs.getInt("providerId"));
                billList.add(_bill);
            }
        }
        BaseDao.closeResource(null, pstm, rs);
        return billList;
    }
}
